import tkinter as tk
from tkinter import ttk
import mysql.connector
from myspider.settings import MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE
import logging

# 配置日志记录
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

def query_database(root, status_label, result_text):
    try:
        status_label.config(text="开始查询数据库...")
        result_text.delete(1.0, tk.END)
        # 连接到 MySQL 数据库，使用 settings.py 中的配置信息
        connection = mysql.connector.connect(
            host=MYSQL_HOST,
            user=MYSQL_USER,
            password=MYSQL_PASSWORD,
            database=MYSQL_DATABASE
        )
        cursor = connection.cursor()

        # 创建新窗口
        query_window = tk.Toplevel(root)
        query_window.title("查询结果")

        # 创建搜索框和按钮
        search_frame = ttk.Frame(query_window)
        search_frame.pack(pady=10)
        search_entry = ttk.Entry(search_frame, width=30)
        search_entry.pack(side=tk.LEFT, padx=5)
        # 插入提示词
        search_entry.insert(0, "name")
        # 绑定点击事件，点击时清除提示词
        search_entry.bind("<FocusIn>", lambda event: clear_placeholder(event, search_entry))
        # 绑定失去焦点事件，若未输入内容则重新显示提示词
        search_entry.bind("<FocusOut>", lambda event: restore_placeholder(event, search_entry))
        search_button = ttk.Button(search_frame, text="搜索", command=lambda: search_name(query_window, cursor, tree, search_entry, status_label, connection))
        search_button.pack(side=tk.LEFT, padx=5)

        # 创建表格
        columns = ('id', 'name', 'url', 'opentime', 'winer', 'pice', 'area', 'actime')
        tree = ttk.Treeview(query_window, columns=columns, show='headings')
        for col in columns:
            tree.heading(col, text=col, command=lambda _col=col: sort_treeview(tree, _col, False))
            if col == 'id':
                tree.column(col, width=25)
            if col == 'name':
                tree.column(col, width=150)
            tree.column(col, width=100)
        tree.pack(pady=20)

        # 定义点击事件处理函数
        def show_details(event):
            # 检查是否有项目被选中
            if tree.selection():
                try:
                    item = tree.selection()[0]
                    values = tree.item(item, 'values')
                    detail_window = tk.Toplevel(query_window)
                    detail_window.title("详细信息")
                    for i, col in enumerate(columns):
                        # 显示列名的标签，设置左对齐
                        ttk.Label(detail_window, text=f"{col}:", anchor=tk.W).grid(row=i, column=0, padx=10, pady=5, sticky=tk.W)
                        # 显示数据的标签，设置左对齐
                        ttk.Label(detail_window, text=values[i], anchor=tk.W).grid(row=i, column=1, padx=10, pady=5, sticky=tk.W)
                except Exception as e:
                    logging.error(f"显示详细信息时出错: {e}")

        # 绑定点击事件
        tree.bind("<ButtonRelease-1>", show_details)

        # 定义排序函数
        def sort_treeview(tree, col, reverse):
            try:
                data = [(tree.set(item, col), item) for item in tree.get_children('')]
                data.sort(reverse=reverse)
                for index, (val, item) in enumerate(data):
                    tree.move(item, '', index)
                tree.heading(col, command=lambda: sort_treeview(tree, col, not reverse))
            except Exception as e:
                logging.error(f"排序表格时出错: {e}")

        # 执行初始查询
        search_term = search_entry.get()
        if search_term and search_term != "name":
            query = "SELECT * FROM scrapy_ WHERE name LIKE %s"
            values = (f"%{search_term}%",)
        else:
            query = "SELECT * FROM scrapy_"
            values = ()
        cursor.execute(query, values)
        results = cursor.fetchall()

        if not results:
            # 如果查询结果为空，显示查询语句
            no_result_label = ttk.Label(query_window, text=f"未找到匹配数据，当前查询语句: {query % values if values else query}")
            no_result_label.pack(pady=20)
        else:
            for row in results:
                tree.insert("", "end", values=row)

        # 定义搜索函数
        def search_name(query_window, cursor, tree, search_entry, status_label, connection):
            search_term = search_entry.get()
            if search_term == "name":
                search_term = ""
            tree.delete(*tree.get_children())
            try:
                if search_term:
                    query = "SELECT * FROM scrapy_ WHERE name LIKE %s"
                    values = (f"%{search_term}%",)
                else:
                    query = "SELECT * FROM scrapy_"
                    values = ()
                cursor.execute(query, values)
                results = cursor.fetchall()
                if not results:
                    # 如果查询结果为空，显示查询语句
                    for widget in query_window.winfo_children():
                        if isinstance(widget, ttk.Label) and "未找到匹配数据" in widget.cget("text"):
                            widget.destroy()
                    no_result_label = ttk.Label(query_window, text=f"未找到匹配数据，当前查询语句: {query % values if values else query}")
                    no_result_label.pack(pady=20)
                else:
                    for widget in query_window.winfo_children():
                        if isinstance(widget, ttk.Label) and "未找到匹配数据" in widget.cget("text"):
                            widget.destroy()
                    for row in results:
                        tree.insert("", "end", values=row)
            except Exception as e:
                status_label.config(text=f"查询数据库发生错误: {e}")

        # 清除提示词函数
        def clear_placeholder(event, entry):
            if entry.get() == "name":
                entry.delete(0, tk.END)

        # 恢复提示词函数
        def restore_placeholder(event, entry):
            if entry.get() == "":
                entry.insert(0, "name")
    except Exception as e:
        status_label.config(text=f"查询数据库发生错误: {e}")

